
package DatabaseAccess;

import java.io.FileInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;


public class DBHelper 
{    
    private static Connection conn = null;    
    
    /** Creates a new instance of DBHelper */
    public DBHelper() 
    {
        if(conn == null)
        {
            try{               
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;user=sa;password=123456;database=QLK");
               
            }catch (ClassNotFoundException e){
                System.out.println(e);
            }catch (SQLException sql){
                System.out.println(sql);
            }            
        }
    }
   
    
    
    /**
     *Method executes a non-parameter selecting querry
     *@param String spName
     *@return ResultSet cs.executeQuery()     
     */
    public static ResultSet executeQuery(String spName) throws SQLException
    {
        if(conn != null)
        {
            CallableStatement cs = conn.prepareCall("{call " + spName + "}");            
            return cs.executeQuery();
        }
        return null;
    }
    
    /**
     *Method executes multi-parameter selecting querry
     *@param String spName, Vector paramList
     *@return ResultSet cst.executeQuery()     
     */
    public static ResultSet executeQuery(String spName, Vector paramList) throws SQLException
    {
        if(conn != null)
        {
            String strQ = "{call " + spName + "(";
            int t =0;
            for(Object obj : paramList)
            {
                if(t != 0)
                    strQ += ",";
                if(obj instanceof Integer)
                {
                    Integer i = (Integer)obj;
                    strQ += i.toString();
                }else if(obj instanceof String)
                {
                    String s = (String)obj;
                    strQ += "'" + s + "'";
                }
                t++;
            }
            strQ += ")}";
            
            CallableStatement cst = conn.prepareCall(strQ);
            return cst.executeQuery();
        }
        return null;
    }    
        
    /**
     *Method to update and delete one record in table
     *@param String spName, Vector paramList
     *@return int cst.executeUpdate()     
     */
    public static int executeUpdate(String spName, Vector paramList) throws SQLException
    {
        if(conn != null)
        {
            String strQ = "{call " + spName + "(";
            
            int t = 0;
            for(Object obj : paramList)
            {
                if(t != 0)
                    strQ += ",";
                if(obj instanceof Integer)
                {
                    Integer i = (Integer)obj;
                    strQ += i.toString();
                }
                else if(obj instanceof Float)
                {
                    Float f = (Float)obj;
                    strQ += f.toString();
                }
                else if(obj instanceof String)
                {
                    String s = (String)obj;
                    strQ += "'" + s + "'";
                }
                t++;
            }
            strQ += ")}";
            
            CallableStatement cst = conn.prepareCall(strQ);
            return cst.executeUpdate();
        }
        return -1;
    }
    
    public static void main(String[] a)
    {
        new DBHelper();
    }
   
}


